package com.food.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.CourseDAO;
import com.food.model.Course;

public class CourseDAOJdbc implements CourseDAO {

//	private static final String URL = "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
//	private static final String USER = "sa";
//	private static final String PASSWORD = "sa123456";
	
	private DataSource ds = null;
	public CourseDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private static final String INSERT = "insert into course  (cname, cteacher, cstartdate, cenddate, cpeople, cplace, cprice, cintroduce) values(?,?,?,?,?,?,?,?)";

	@Override
	public void insertCourse(Course bean) {

		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(INSERT);

			stmt.setString(1, bean.getCname());
			stmt.setString(2, bean.getCteacher());
			stmt.setDate(3, bean.getCstartdate());
			stmt.setDate(4, bean.getCenddate());
			stmt.setInt(5, bean.getCpeople());
			stmt.setString(6, bean.getCplace());
			stmt.setInt(7, bean.getCprice());
			stmt.setString(8, bean.getCintroduce());


			
			int i = stmt.executeUpdate();
			if (i == 1) {
				this.findOneCourse(bean.getCid());
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String UPDATE = "update course set cname =?, cteacher =?, cstartdate =?, cenddate =?, cpeople =?, cplace =?, cprice =?, cintroduce =? where cid=?";

	@Override
	public void updateCourse(String cname, String cteacher, Date cstartdate,
			Date cenddate, int cpeople, String cplace, int cprice,
			String cintroduce, int cid) {

		Course result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(UPDATE);

			stmt.setString(1, cname);
			stmt.setString(2, cteacher);
			stmt.setDate(3, cstartdate);
			stmt.setDate(4, cenddate);
			stmt.setInt(5, cpeople);
			stmt.setString(6, cplace);
			stmt.setInt(7, cprice);
			stmt.setString(8, cintroduce);
			stmt.setInt(9, cid);

			int i = stmt.executeUpdate();
			if (i == 1) {
				result = new Course();
				result.setCid(cid);
				result.setCname(cname);
				result.setCteacher(cteacher);
				result.setCstartdate(cstartdate);
				result.setCenddate(cenddate);
				result.setCpeople(cpeople);
				result.setCplace(cplace);
				result.setCprice(cprice);
				result.setCintroduce(cintroduce);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String DELETE = "delete from course where cid=?";

	@Override
	public void deleteCourse(int cid) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(DELETE);

			stmt.setInt(1, cid);
			
			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String SELECT_BY_Course = "select cid,cname, cteacher, cstartdate, cenddate, cpeople, cplace, cprice, cintroduce from course";

	@Override
	public List<Course> findAllCourse() {
		List<Course> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_Course);

			rset = stmt.executeQuery();

			result = new ArrayList<Course>();
			while (rset.next()) {
				Course temp = new Course();

				temp.setCid(rset.getInt("cid"));
				temp.setCname(rset.getString("cname"));
				temp.setCteacher(rset.getString("cteacher"));
				temp.setCstartdate(rset.getDate("cstartdate"));
				temp.setCenddate(rset.getDate("cenddate"));
				temp.setCpeople(rset.getInt("cpeople"));
				temp.setCplace(rset.getString("cplace"));
				temp.setCprice(rset.getInt("cprice"));
				temp.setCintroduce(rset.getString("cintroduce"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String SELECT_BY_ID = "select cid,cname, cteacher, cstartdate, cenddate, cpeople, cplace, cprice,	cintroduce from course where cid=?";

	@Override
	public Course findOneCourse(int cid) {
		Course result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();

			stmt = conn.prepareStatement(SELECT_BY_ID);
			stmt.setInt(1, cid);
			rset = stmt.executeQuery();
			if (rset.next()) {
				result = new Course();
				result.setCid(rset.getInt("cid"));
				result.setCname(rset.getString("cname"));
				result.setCteacher(rset.getString("cteacher"));
				result.setCstartdate(rset.getDate("cstartdate"));
				result.setCenddate(rset.getDate("cenddate"));
				result.setCpeople(rset.getInt("cpeople"));
				result.setCplace(rset.getString("cplace"));
				result.setCprice(rset.getInt("cprice"));
				result.setCintroduce(rset.getString("cintroduce"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	public static void main(String[] args) {

		CourseDAO dao = new CourseDAOJdbc();
		// Course bean = dao.findOneCourse(1);
		// System.out.println(bean);
		// System.out.println(bean.getCid());
		// System.out.println(bean.getCname());

		// SELSCT all
		// List<Course> bean1 = dao.findAllCourse();
		// System.out.println(bean1);

		// INSERT ???
		 Course bean2 = new Course();
		 bean2.setCenddate(new java.sql.Date(0));
		 bean2.setCintroduce("japan food");
		 bean2.setCprice(147258);
		 bean2.setCname("asdf");
		 dao.insertCourse(bean2);
		 System.out.println(bean2);



		// delete
		// dao.deleteCourse(4);
	}

}
